const Knex = require('knex')
const baseConfig =  require('../base-config')
const logger = require('../utils/console-logger')

const config = {
    client: 'sqlite3',
    useNullAsDefault: true,
    connection: {
        filename: baseConfig.dbUrl
    },
    pool: {
        min: 0,
        max: 7
    }
}

const knex = Knex(config)

class Base {
    constructor(table, columnMap) {
        this.table = table
        this.columnMap = columnMap
        this.knex = knex
    }
    // 字段名转换 数据库->系统
    warpModel(model) {
        const exposeModel = {
            ...model
        }
        if (this.columnMap) {
            for (const key in this.columnMap) {
                exposeModel[key] = model[this.columnMap[key]]
                delete exposeModel[this.columnMap[key]]
            }
        }
        return exposeModel
    }
    // 字段名转换 系统->数据库
    unWarpModel(model) {
        const exposeModel = {
            ...model
        }
        if (this.columnMap) {
            for (const key in this.columnMap) {
                exposeModel[this.columnMap[key]] = model[key]
                delete exposeModel[key]
            }
        }
        return exposeModel
    }

    //
    buildPaging(params) {
        const { current=1, pageSize=20, sortField, sortOrder } = params

        const paging = {
            current: parseInt(current),
            pageSize: parseInt(pageSize), 
            sortField,
            sortOrder: ('descend' === sortOrder ? 'desc' : 'asc')  // "ascend" "descend"
        }

        return paging
    }

    // 列表查询
    async list(paging, whereBuilder, limit) {
        const listQuery = this.knex.select().from(this.table)
        if (limit && !isNaN(limit)) listQuery.limit(limit)
        paging.sortField && listQuery.orderBy(paging.sortField, paging.sortOrder)
        whereBuilder && whereBuilder(listQuery)
        logger.debug('list sql: ', listQuery.toString())
        const rows = await listQuery
        return rows ? rows.map(row=>this.warpModel(row)) : []
    }
    // 查询数量
    async count(whereBuilder) {
        const countQuery = this.knex.count().from( this.table)
        whereBuilder && whereBuilder(countQuery)
        logger.debug('count sql: ', countQuery.toString())
        const counts = await countQuery
        return counts?counts[0]['count(*)'] : 0
    }
    // 查询数量
    async countBy(by) {
        const countQuery = this.knex.count().from( this.table).where(by)
        logger.debug('countBy sql: ', countQuery.toString())
        const counts = await countQuery
        return counts?counts[0]['count(*)'] : 0
    }
    // 分页查询
    async page(paging, whereBuilder, columns = []) {
        const total = await this.count(whereBuilder)
        const pageQuery = this.knex.select(columns)
        pageQuery.from(this.table).limit(paging.pageSize).offset((paging.current-1)*paging.pageSize)
        paging.sortField && pageQuery.orderBy(paging.sortField, paging.sortOrder)
        whereBuilder && whereBuilder(pageQuery)
        logger.debug('page sql: ', pageQuery.toString())
        const rows = await pageQuery
        return {
            current: paging.current,
            pageSize: paging.pageSize,
            total,
            rows: rows ? rows.map(row=>this.warpModel(row)) : []
        }
    }
    // 根据id获取
    async getById(id) {
        const getByIdQuery = this.knex.select().from(this.table).where('id', id)
        logger.debug('getById sql: ', getByIdQuery.toString())
        const rows = await getByIdQuery
        return rows && rows.length > 0 ? this.warpModel(rows[0]) : {}
    }
    // 插入
    // columns = {col: val[,...]}
    async insert(columns) {
        const insertQuery = this.knex(this.table).insert(this.unWarpModel(columns))
        logger.debug('insert sql: ', insertQuery.toString())
        const inserted = await insertQuery
        return inserted
    }
    // 更新
    // columns = {col: val[,...]}    
    // by = {col: val[,...]}
    async updateBy(columns, by) {
        const updateQuery = this.knex(this.table).where(by).update(this.unWarpModel(columns))
        logger.debug('updateBy sql: ', updateQuery.toString())
        const updated = await updateQuery
        return updated
    }
    // 删除
    // by = {col: val[,...]}
    async delete(by) {
        const deleteQuery = this.knex(this.table).delete().where(by)
        logger.debug('delete sql: ', deleteQuery.toString())
        const deleted = await deleteQuery
        return deleted
    }
}

module.exports = Base
